I haven't tried this in Access, but why not fetch the record
identifier first then insert the record like below. Retrieving the id
first makes sure that you get the right id, because you might be in a
pooled connection environment, and you might not get the right id
after the insert.
SELECT nextval('schema.sequence_for_table_record_id') AS record_id;
INSERT INTO test (record_id,code) VALUES ([retrieved record_id],20);
or
INSERT INTO test (record_id,code) VALUES
(currval('schema.sequence_for_table_record_id'),20);
Ray A.
>Hello,
>
>I have made some more tests regarding my precedent post "[ODBC]
>Access - ODBC - index strange bug", and in fact it is not
>index-related. The bug is just more visible when an index is used,
>apparently.
>
>The problem comes from the serial identifier, which is not know from
>the client at the time the record is being inserted.
>
>With the table described below, imagine I do, from the client:
>
>insert into test (code) VALUES (20);
>
>How does the client know the id that has been given to the record?
>With ethereal, I could see Access fetches the id by doing a
>
>select id from test where code = 20"
>
>Of course, another record has the same code, and the wrong id is
>being fetched back. This explains what I have noticed, and that is
>explained below...
>
>Is there a solution to that, except inserting records with PL-PGSQL,
>which I already do in some cases, by the way...?
>
>Is that an Access-related problem? Does it work with SQL Server?
>
>
>Thanks for your feedback...
>
>Philippe
>
